#!/usr/bin/python
# -*- coding : UTF-8 -*-
# ========================
# @Time  : 2020/5/12 17:01
# @Author: Mecell
# @File  : mysql_packaging.py
# ========================

import pymysql
import os

from common.config_packaging import ConfigHandle
from common.log_packaging import logger
from common.path_constants import BASIC_INFO_CONF, QUERY_SQL_CONF
from data.com_function import generate_random_no




class MysqlHandle:
    """
    定义一个连接MySQL查询数据库的类
    """
    def __init__(self):
        basic_conf = ConfigHandle(BASIC_INFO_CONF)
        self.query_conf = ConfigHandle(QUERY_SQL_CONF)
        self.connection = pymysql.connect(host=basic_conf("mysql", "host"),
                                          user = basic_conf("mysql", "user"),
                                          password = basic_conf("mysql", "password"),
                                          db=basic_conf("mysql", "db"),
                                          port=basic_conf("mysql", "port"),
                                          charset=basic_conf("mysql", "charset"),
                                          cursorclass=pymysql.cursors.DictCursor)
        self.cursor = self.connection.cursor()

    def __call__(self, sql, args=None, get_result=True, fetch_all=False):
        """
        使用对象调用
        :param sql: 执行的SQL语句
        :param arg: sql语句中的参数，序列类型tuple, list or dict
        :param fetch_all: 读取所有查询结果标签
        :return: 查询结果
        """
        self.cursor.execute(sql, args)
        self.connection.commit()
        if get_result:
            if fetch_all:
                return self.cursor.fetchall() # 返回所有记录组成的嵌套字典的列表
            else:
                return self.cursor.fetchone() # 返回所有记录中第一条记录组成的字典
        else:
            pass

    @staticmethod
    def generate_emp_num():
        """
        生成随机工号
        :return:
        """
        emp_num = "0" + generate_random_no(5)
        return emp_num


    def is_avail_emp_num(self, emp_num):
        """
        判断工号是否存在\可用
        :param emp_num:
        :return:
        """
        emp_num_sql = self.query_conf("login", "avail_emp_num")
        if self(emp_num_sql, args=(0, 1, emp_num)):
            return True
        else:
            return False

    def is_lock_emp_num(self, emp_num):
        """
        判断工号是否为锁定状态
        :param emp_num:
        :return:
        """
        emp_num_sql = self.query_conf("login", "lock_emp_num")
        if self(emp_num_sql, args=(3, 0, emp_num)):
            return True
        else:
            return False

    def generate_unavail_emp_num(self):
        """
        随机生成不存在\不可用的工号
        :return:
        """
        while True:
            emp_num = self.generate_emp_num()
            if not self.is_avail_emp_num(emp_num):
                break
        return emp_num

    def generate_avail_emp_num(self):
        """
        随机生成存在\可用的工号
        :return:
        """
        while True:
            emp_num = self.generate_emp_num()
            if self.is_avail_emp_num(emp_num):
                break
        return emp_num

    def generate_lock_emp_num(self):
        """
        随机生成锁定的工号
        :return:
        """
        while True:
            emp_num = self.generate_emp_num()
            if self.is_lock_emp_num(emp_num):
                break
        return emp_num

    def get_emp_info(self, emp_num):
        """
        获取员工信息和用户id
        :param emp_num:
        :return:
        """
        emp_info_sql = self.query_conf("login", "emp_info")
        emp_info = self(emp_info_sql, args=(emp_num))
        # return emp_info["emp_name"], emp_info["user_id"], emp_info['department_name']
        return emp_info


    def get_avail_emp_info(self):
        """
        获取随机生成可用工号的员工工号、姓名
        :return:
        """
        emp_num = self.generate_avail_emp_num()
        emp_name = self.get_emp_info(emp_num)['emp_name']
        user_id = self.get_emp_info(emp_num)['user_id']
        emp_depart = self.get_emp_info(emp_num)['department_name']
        return emp_num, emp_name, user_id, emp_depart

    def get_cur_emp_info(self):
        """
        获取在职员工工号姓名及部门
        :return:
        """
        cur_emp_info_sql = self.query_conf("emp", "emp_info")
        cur_emp_info = self(cur_emp_info_sql, args=(1,1))
        return cur_emp_info

    def get_ordinary_emp_info(self, args=(1,363)):
        """
        获取普通员工角色的员工用户名和用户id
        :param args: 只有一个角色且为可用状态
        :return:
        """
        role_sql = self.query_conf("role", "ordinary_emp_role")
        emp_info = self(role_sql, args)
        return emp_info

    def close_sql(self):
        """
        关闭SQL连接
        :return:
        """
        try:
            if self.cursor:
                self.cursor.close()
        except:
            logger.exception("关闭游标异常")
        finally:
            try:
                if self.connection:
                    self.connection.close()
            except:
                logger.exception("关闭数据库连接异常")



if __name__ == '__main__':
    mysql_obj = MysqlHandle()

    # print(mysql_obj.get_emp_info('013257'))
    #print(mysql_obj.get_cur_emp_info())
    # print(mysql_obj.get_emp_info('009961'))
    # print(mysql_obj.get_cur_emp_info())
    # print(mysql_obj.get_ordinary_emp_info(args=(1,1))) # 只有一个角色且为可用状态
    # sql_config = ConfigHandle(QUERY_SQL_CONF)
    # print(mysql_obj.get_avail_emp_info())
    # print(mysql_obj.get_ordinary_emp_info()) # 只有一个角色且为可用状态
    # print(mysql_obj("SELECT assets_id FROM `t_assets_classification` WHERE assets_classify_code = 'MACBJB';") == None)
    sql = """SELECT
        	t1.department_level_id,
    	t1.department_id,
    	t3.department_name
    FROM
	    t_employee t1
    INNER JOIN t_department t2 ON t2.department_id = t1.department_level_id
    INNER JOIN t_department t3 ON t3.department_id = t1.department_id
    WHERE
        t2.department_id = '75'
    GROUP BY
        t1.department_id
     """

    data = mysql_obj(sql=sql,fetch_all=True)
    print(data)
    # for i in data:
    #     name.append(i['department_name'])
    # print(str(name))
    a = ''
    for i in range(0,len(data)):
        a = a+str(data[i]['department_id']) + ','
    print(a)



    # data = mysql_obj(sql=sql_config("attendance","copy_emp_id"),args=("008770",))['emp_id']
    # print(data)
    # mysql_obj.close_sql()



